﻿SET QUOTED_IDENTIFIER OFF;
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Assignments_Customers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Assignments] DROP CONSTRAINT [FK_Assignments_Customers];
GO
IF OBJECT_ID(N'[dbo].[FK_Assignments_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Assignments] DROP CONSTRAINT [FK_Assignments_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Categories_Categories]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Categories] DROP CONSTRAINT [FK_Categories_Categories];
GO
IF OBJECT_ID(N'[dbo].[FK_Comments_Reports]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_Comments_Reports];
GO
IF OBJECT_ID(N'[dbo].[FK_Comments_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_Comments_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Reports_Categories]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_Reports_Categories];
GO
IF OBJECT_ID(N'[dbo].[FK_Reports_Customers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_Reports_Customers];
GO
IF OBJECT_ID(N'[dbo].[FK_Reports_NotificationTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_Reports_NotificationTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Reports_Status]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_Reports_Status];
GO
IF OBJECT_ID(N'[dbo].[FK_Reports_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_Reports_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Status_Customers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Status] DROP CONSTRAINT [FK_Status_Customers];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Assignments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Assignments];
GO
IF OBJECT_ID(N'[dbo].[Categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Categories];
GO
IF OBJECT_ID(N'[dbo].[Comments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comments];
GO
IF OBJECT_ID(N'[dbo].[Customers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Customers];
GO
IF OBJECT_ID(N'[dbo].[NotificationTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NotificationTypes];
GO
IF OBJECT_ID(N'[dbo].[Reports]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Reports];
GO
IF OBJECT_ID(N'[dbo].[Status]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Status];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [CategoryID] int IDENTITY(1,1) NOT NULL,
    [CategoryCustomerID] int  NOT NULL,
    [CategoryTitle] nvarchar(255)  NOT NULL,
    [CategoryKey] nvarchar(50)  NOT NULL,
    [CategoryDescription] nvarchar(2000)  NULL,
    [CategoryImageUrl] nvarchar(255)  NULL,
    [CategoryColor] nvarchar(6)  NULL
);
GO

-- Creating table 'NotificationTypes'
CREATE TABLE [dbo].[NotificationTypes] (
    [NotificationTypeID] int IDENTITY(1,1) NOT NULL,
    [NotificationTypeKey] nvarchar(50)  NOT NULL,
    [NotificationTypeTitle] nvarchar(255)  NOT NULL
);
GO

-- Creating table 'Status'
CREATE TABLE [dbo].[Status] (
    [StatusID] int IDENTITY(1,1) NOT NULL,
    [StatusTitle] nvarchar(255)  NOT NULL,
    [StatusKey] nvarchar(50)  NOT NULL,
    [StatusImageUrl] nvarchar(255)  NOT NULL,
    [StatusCustomerID] int  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserID] int IDENTITY(1,1) NOT NULL,
    [UserNickname] nvarchar(255)  NOT NULL,
    [UserIdentityProvider] nvarchar(255)  NOT NULL,
    [UserNameIdentifier] nvarchar(255)  NOT NULL,
    [UserImageUrl] nvarchar(255)  NOT NULL
);
GO

-- Creating table 'Reports'
CREATE TABLE [dbo].[Reports] (
    [ReportID] int IDENTITY(1,1) NOT NULL,
    [ReportCustomerID] int  NOT NULL,
    [ReportTitle] nvarchar(255)  NOT NULL,
    [ReportWhen] datetime  NOT NULL,
    [ReportBy] int  NOT NULL,
    [ReportStatusID] int  NOT NULL,
    [ReportStatusWhen] datetime  NOT NULL,
    [ReportCategoryID] int  NOT NULL,
    [ReportLatitude] float  NOT NULL,
    [ReportLongitude] float  NOT NULL,
    [ReportLocation] nvarchar(2000)  NOT NULL,
    [ReportImageUrl] nvarchar(255)  NOT NULL,
    [ReportNotificationTypeID] int  NOT NULL,
    [ReportNotificationDetails] nvarchar(255)  NOT NULL,
    [ReportFixedImageUrl] nvarchar(255)  NULL
);
GO

-- Creating table 'Comments'
CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY(1,1) NOT NULL,
    [CommentText] varchar(max)  NOT NULL,
    [CommentRating] float  NOT NULL,
    [CommentBy] int  NOT NULL,
    [CommentFor] int  NOT NULL,
    [CommentWhen] datetime  NOT NULL
);
GO

-- Creating table 'Assignments'
CREATE TABLE [dbo].[Assignments] (
    [AssignmentCustomerID] int  NOT NULL,
    [AssignmentUserID] int  NOT NULL,
    [AssignmentRole] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Customers'
CREATE TABLE [dbo].[Customers] (
    [CustomerID] int IDENTITY(1,1) NOT NULL,
    [CustomerName] nvarchar(255)  NOT NULL,
    [CustomerLogoUrl] nvarchar(1000)  NOT NULL,
    [CustomerLatitude] float  NOT NULL,
    [CustomerLongitude] float  NOT NULL,
    [CustomerHostname] nvarchar(255)  NOT NULL,
    [CustomerZoom] int  NOT NULL,
    [CustomerMapCredentials] nvarchar(255)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CategoryID] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([CategoryID] ASC);
GO

-- Creating primary key on [NotificationTypeID] in table 'NotificationTypes'
ALTER TABLE [dbo].[NotificationTypes]
ADD CONSTRAINT [PK_NotificationTypes]
    PRIMARY KEY CLUSTERED ([NotificationTypeID] ASC);
GO

-- Creating primary key on [StatusID] in table 'Status'
ALTER TABLE [dbo].[Status]
ADD CONSTRAINT [PK_Status]
    PRIMARY KEY CLUSTERED ([StatusID] ASC);
GO

-- Creating primary key on [UserID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserID] ASC);
GO

-- Creating primary key on [ReportID] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [PK_Reports]
    PRIMARY KEY CLUSTERED ([ReportID] ASC);
GO

-- Creating primary key on [CommentID] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [PK_Comments]
    PRIMARY KEY CLUSTERED ([CommentID] ASC);
GO

-- Creating primary key on [AssignmentCustomerID], [AssignmentUserID] in table 'Assignments'
ALTER TABLE [dbo].[Assignments]
ADD CONSTRAINT [PK_Assignments]
    PRIMARY KEY CLUSTERED ([AssignmentCustomerID], [AssignmentUserID] ASC);
GO

-- Creating primary key on [CustomerID] in table 'Customers'
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [PK_Customers]
    PRIMARY KEY CLUSTERED ([CustomerID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ReportCategoryID] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_Categories]
    FOREIGN KEY ([ReportCategoryID])
    REFERENCES [dbo].[Categories]
        ([CategoryID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Reports_Categories'
CREATE INDEX [IX_FK_Reports_Categories]
ON [dbo].[Reports]
    ([ReportCategoryID]);
GO

-- Creating foreign key on [ReportNotificationTypeID] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_NotificationTypes]
    FOREIGN KEY ([ReportNotificationTypeID])
    REFERENCES [dbo].[NotificationTypes]
        ([NotificationTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Reports_NotificationTypes'
CREATE INDEX [IX_FK_Reports_NotificationTypes]
ON [dbo].[Reports]
    ([ReportNotificationTypeID]);
GO

-- Creating foreign key on [ReportStatusID] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_Status]
    FOREIGN KEY ([ReportStatusID])
    REFERENCES [dbo].[Status]
        ([StatusID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Reports_Status'
CREATE INDEX [IX_FK_Reports_Status]
ON [dbo].[Reports]
    ([ReportStatusID]);
GO

-- Creating foreign key on [ReportBy] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_Users]
    FOREIGN KEY ([ReportBy])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Reports_Users'
CREATE INDEX [IX_FK_Reports_Users]
ON [dbo].[Reports]
    ([ReportBy]);
GO

-- Creating foreign key on [CommentFor] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_Comments_Reports]
    FOREIGN KEY ([CommentFor])
    REFERENCES [dbo].[Reports]
        ([ReportID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comments_Reports'
CREATE INDEX [IX_FK_Comments_Reports]
ON [dbo].[Comments]
    ([CommentFor]);
GO

-- Creating foreign key on [CommentBy] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_Comments_Users]
    FOREIGN KEY ([CommentBy])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comments_Users'
CREATE INDEX [IX_FK_Comments_Users]
ON [dbo].[Comments]
    ([CommentBy]);
GO

-- Creating foreign key on [AssignmentUserID] in table 'Assignments'
ALTER TABLE [dbo].[Assignments]
ADD CONSTRAINT [FK_Assignments_Users]
    FOREIGN KEY ([AssignmentUserID])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Assignments_Users'
CREATE INDEX [IX_FK_Assignments_Users]
ON [dbo].[Assignments]
    ([AssignmentUserID]);
GO

-- Creating foreign key on [AssignmentCustomerID] in table 'Assignments'
ALTER TABLE [dbo].[Assignments]
ADD CONSTRAINT [FK_Assignments_Customers]
    FOREIGN KEY ([AssignmentCustomerID])
    REFERENCES [dbo].[Customers]
        ([CustomerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CategoryCustomerID] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [FK_Categories_Categories]
    FOREIGN KEY ([CategoryCustomerID])
    REFERENCES [dbo].[Customers]
        ([CustomerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Categories_Categories'
CREATE INDEX [IX_FK_Categories_Categories]
ON [dbo].[Categories]
    ([CategoryCustomerID]);
GO

-- Creating foreign key on [ReportCustomerID] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_Customers]
    FOREIGN KEY ([ReportCustomerID])
    REFERENCES [dbo].[Customers]
        ([CustomerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Reports_Customers'
CREATE INDEX [IX_FK_Reports_Customers]
ON [dbo].[Reports]
    ([ReportCustomerID]);
GO

-- Creating foreign key on [StatusCustomerID] in table 'Status'
ALTER TABLE [dbo].[Status]
ADD CONSTRAINT [FK_Status_Customers]
    FOREIGN KEY ([StatusCustomerID])
    REFERENCES [dbo].[Customers]
        ([CustomerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Status_Customers'
CREATE INDEX [IX_FK_Status_Customers]
ON [dbo].[Status]
    ([StatusCustomerID]);
GO


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[LogEntries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogEntries];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'LogEntries'
CREATE TABLE [dbo].[LogEntries] (
    [LogEntryID] int IDENTITY(1,1) NOT NULL,
    [LogEntryCategory] nvarchar(100)  NULL,
    [LogEntryMessage] nvarchar(4000)  NULL,
    [LogEntryException] nvarchar(max)  NULL,
    [LogEntrySeverity] nvarchar(2000)  NULL,
    [LogEntryWhen] datetime  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [LogEntryID] in table 'LogEntries'
ALTER TABLE [dbo].[LogEntries]
ADD CONSTRAINT [PK_LogEntries]
    PRIMARY KEY CLUSTERED ([LogEntryID] ASC);
GO

insert into notificationtypes (notificationtypekey, notificationtypetitle) values ('notification1', 'Notification 1');
insert into notificationtypes (notificationtypekey, notificationtypetitle) values ('notification2', 'Notification 2');

GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------